//
//  DataHandle.m
//  A1.0
//
//  Created by lanou3g on 15-4-7.
//  Copyright (c) 2015年 ccandxx. All rights reserved.
//

#import "DataHandle.h"
#import <sqlite3.h>
#import "jokeModel.h"
#import "picModel.h"
#import "womenModel.h"
#import "videoModel.h"
#import "petModel.h"


static DataHandle *dataHandle = nil;
@implementation DataHandle

//单例类方法
+(instancetype)shareDataBase
{
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        dataHandle = [[DataHandle alloc] init];
    });
    return dataHandle;
}


static sqlite3 *db = nil;

//打开数据库
-(void)openDB
{
    if (db != nil) {
        NSLog(@"数据库已经打开");
        return;
    }
    NSString *dbPath = [[self documentsPath] stringByAppendingPathComponent:@"Joke.sqlite"];
    int result = sqlite3_open(dbPath.UTF8String, &db);
    if (result == SQLITE_OK ) {
        NSLog(@"数据库创建成功");
    }else{
        NSLog(@"数据库创建失败");
    }

}


//获取Documents路径
-(NSString *)documentsPath
{
    return NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0];
}



//关闭数据库
-(void)closeDB
{
    int reselt = sqlite3_close(db);
    if (reselt == SQLITE_OK) {
        db = nil;
        NSLog(@"数据库关闭成功");
    } else {
        db = nil;
        NSLog(@"数据库关闭失败");
    }

}


//建表


//笑话
-(void)createJokeTable
{
    NSString *creSql = @"CREATE  TABLE  IF NOT EXISTS Joke (jid INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , jtitle TEXT)";
    
    int result = sqlite3_exec(db, creSql.UTF8String, NULL, NULL, NULL);
    
    if (result == SQLITE_OK) {
        NSLog(@"建表成功");
    }else{
        NSLog(@"建表失败");
    }

}

//趣图
-(void)createPicTable
{
    NSString *creSql = @"CREATE  TABLE  IF NOT EXISTS Funny (fid INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , ftitle TEXT)";
    int result = sqlite3_exec(db, creSql.UTF8String, NULL, NULL, NULL);
    
    if (result == SQLITE_OK) {
        NSLog(@"建表成功");
    }else{
        NSLog(@"建表失败");
    }

}

//美女
-(void)createWomenTable
{
    NSString *creSql = @"CREATE  TABLE  IF NOT EXISTS Women (wid INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , wtitle TEXT)";
    int result = sqlite3_exec(db, creSql.UTF8String, NULL, NULL, NULL);
    
    if (result == SQLITE_OK) {
        NSLog(@"建表成功");
    }else{
        NSLog(@"建表失败");
    }

}


//视频
-(void)createVideoTable
{
    NSString *creSql = @"CREATE  TABLE  IF NOT EXISTS Video (vid INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , vtitle TEXT)";
    int result = sqlite3_exec(db, creSql.UTF8String, NULL, NULL, NULL);
    
    if (result == SQLITE_OK) {
        NSLog(@"建表成功");
    }else{
        NSLog(@"建表失败");
    }

}

//宠物

-(void)createPetTable
{
    NSString *creSql = @"CREATE  TABLE  IF NOT EXISTS Pet (pid INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , ptitle TEXT)";
    int result = sqlite3_exec(db, creSql.UTF8String, NULL, NULL, NULL);
    
    if (result == SQLITE_OK) {
        NSLog(@"建表成功");
    }else{
        NSLog(@"建表失败");
    }

}




//插入数据(增)

//笑话
-(void)insertJoke:(jokeModel *)aJoke
{
    NSString *insertsql = [NSString stringWithFormat:@"INSERT INTO Joke (jtitle) VALUES ('%@')",aJoke.title];
    int result = sqlite3_exec(db, insertsql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"数据增加成功");
    }else{
        NSLog(@"数据增加失败");
    }

}
//趣图
-(void)insertPic:(picModel *)aFun
{
    NSString *insertsql = [NSString stringWithFormat:@"INSERT INTO Funny (ftitle) VALUES ('%@')",aFun.title];
    NSString *insertsqlpic = [NSString stringWithFormat:@"INSERT INTO Funny (ftitle) VALUES ('%@')",aFun.pic];
    NSString *insertsqlpich = [NSString stringWithFormat:@"INSERT INTO Funny (ftitle) VALUES ('%f')",aFun.pic_h];
    NSString *insertsqlpicw = [NSString stringWithFormat:@"INSERT INTO Funny (ftitle) VALUES ('%f')",aFun.pic_w];

    
    int result = sqlite3_exec(db, insertsql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"数据增加成功");
    }else{
        NSLog(@"数据增加失败");
    }

}
//美女
-(void)insertWomen:(womenModel *)aWomen
{
    NSString *insertsql = [NSString stringWithFormat:@"INSERT INTO Women (wtitle) VALUES ('%@')",aWomen.title];
    int result = sqlite3_exec(db, insertsql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"数据增加成功");
    }else{
        NSLog(@"数据增加失败");
    }

}
//视频
-(void)insertVideo:(videoModel *)aVideo
{
    NSString *insertsql = [NSString stringWithFormat:@"INSERT INTO Video (vtitle) VALUES ('%@')",aVideo.title];
    int result = sqlite3_exec(db, insertsql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"数据增加成功");
    }else{
        NSLog(@"数据增加失败");
    }

}
//宠物
-(void)insertPet:(petModel *)aPet
{
    NSString *insertsql = [NSString stringWithFormat:@"INSERT INTO Pet (ptitle) VALUES ('%@')",aPet.text];
    int result = sqlite3_exec(db, insertsql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"数据增加成功");
    }else{
        NSLog(@"数据增加失败");
    }

}

//删除数据(删)
-(void)deleteWithJokeID:(NSInteger)jid
{
    NSString *delSql = [NSString stringWithFormat:@"DELETE FROM Joke WHERE jid = '%ld'",jid];
    int result = sqlite3_exec(db, delSql.UTF8String, NULL, NULL, NULL);
    
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    }else{
        NSLog(@"删除失败");
    }

}
-(void)deleteWithPicID:(NSInteger)fid
{
    NSString *delSql = [NSString stringWithFormat:@"DELETE FROM Funny WHERE fid = '%ld'",fid];
    int result = sqlite3_exec(db, delSql.UTF8String, NULL, NULL, NULL);
    
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    }else{
        NSLog(@"删除失败");
    }
}
-(void)deleteWithWomenID:(NSInteger)wid
{
    NSString *delSql = [NSString stringWithFormat:@"DELETE FROM Women WHERE wid = '%ld'",wid];
    int result = sqlite3_exec(db, delSql.UTF8String, NULL, NULL, NULL);
    
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    }else{
        NSLog(@"删除失败");
    }
}
-(void)deleteWithVideoID:(NSInteger)vid
{
    NSString *delSql = [NSString stringWithFormat:@"DELETE FROM Video WHERE vid = '%ld'",vid];
    int result = sqlite3_exec(db, delSql.UTF8String, NULL, NULL, NULL);
    
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    }else{
        NSLog(@"删除失败");
    }
}
-(void)deleteWithPetID:(NSInteger)pid
{
    NSString *delSql = [NSString stringWithFormat:@"DELETE FROM Pet WHERE pid = '%ld'",pid];
    int result = sqlite3_exec(db, delSql.UTF8String, NULL, NULL, NULL);
    
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    }else{
        NSLog(@"删除失败");
    }
}


//查找数据(查)

//1.全查
-(NSMutableArray *)selectAllJoke
{
    NSMutableArray *jokeArray = nil;
    NSString *selSql = @"SELECT * FROM Joke";
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare(db, selSql.UTF8String, -1, &stmt, nil);
    if (result == SQLITE_OK) {
        jokeArray = [NSMutableArray array];
        
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            NSInteger jid = sqlite3_column_int(stmt, 0);
            NSString *jtitle = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];
            
            jokeModel *j = [[jokeModel alloc] init];
            j.title = jtitle;
            j.jid = jid;
            [jokeArray addObject:j];
            
        }
    }
    sqlite3_finalize(stmt);
    
    return jokeArray;
}

-(NSMutableArray *)selectAllFun
{
    NSMutableArray *funArray = nil;
    NSString *selSql = @"SELECT * FROM Funny";
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare(db, selSql.UTF8String, -1, &stmt, nil);
    if (result == SQLITE_OK) {
        funArray = [NSMutableArray array];
        
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            NSInteger fid = sqlite3_column_int(stmt, 0);
            NSString *ftitle = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];
            
            picModel *fun = [[picModel alloc] init];
            fun.title = ftitle;
            fun.fid = fid;
            [funArray addObject:fun];
            
        }
    }
    sqlite3_finalize(stmt);
    
    return funArray;

}
-(NSMutableArray *)selectAllWomen
{
    NSMutableArray *womenArray = nil;
    NSString *selSql = @"SELECT * FROM Women";
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare(db, selSql.UTF8String, -1, &stmt, nil);
    if (result == SQLITE_OK) {
        womenArray = [NSMutableArray array];
        
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            NSInteger wid = sqlite3_column_int(stmt, 0);
            NSString *wtitle = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];
            
            womenModel *w = [[womenModel alloc] init];
            w.title = wtitle;
            w.wid = wid;
            [womenArray addObject:w];
            
        }
    }
    sqlite3_finalize(stmt);
    
    return womenArray;

}
-(NSMutableArray *)selectAllVideo
{
    NSMutableArray *videoArray = nil;
    NSString *selSql = @"SELECT * FROM Video";
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare(db, selSql.UTF8String, -1, &stmt, nil);
    if (result == SQLITE_OK) {
        videoArray = [NSMutableArray array];
        
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            NSInteger vid = sqlite3_column_int(stmt, 0);
            NSString *vtitle = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];
            
            videoModel *v = [[videoModel alloc] init];
            v.title = vtitle;
            v.vid = vid;
            [videoArray addObject:v];
            
        }
    }
    sqlite3_finalize(stmt);
    
    return videoArray;

}
-(NSMutableArray *)selectAllPet
{
    NSMutableArray *petArray = nil;
    NSString *selSql = @"SELECT * FROM Pet";
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare(db, selSql.UTF8String, -1, &stmt, nil);
    if (result == SQLITE_OK) {
        petArray = [NSMutableArray array];
        
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            NSInteger pid = sqlite3_column_int(stmt, 0);
            NSString *ptitle = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];
            
            petModel *pet = [[petModel alloc] init];
            pet.text = ptitle;
            pet.pid = pid;
            [petArray addObject:pet];
            
        }
    }
    sqlite3_finalize(stmt);
    
    return petArray;

}



@end
